# -*- encoding:utf-8 -*-
"""
@作者：ven
@文件名：mysqlUtil.py
@时间：2021-11-22  14:32
@文档说明:
# 需要下载mysql-connector，mysql-connector是MySQL官方提供的驱动器
# 下载命令：pip install mysql-connector
"""
import json
import traceback

import time

import pymysql
from config.config import MYSQL_DB
from utils.loguruUtil import log


class MysqlConnectDatabase:
    # 连接数据库
    def __init__(self, charset='utf8'):
        self.__host = MYSQL_DB['host']
        self.__port = MYSQL_DB['port']  # mysql端口
        self.__username = MYSQL_DB['user']  # mysql远程连接用户名
        self.__password = MYSQL_DB['password']  # mysql远程连接密码
        self.__db = MYSQL_DB['db']  # mysql使用的数据库名
        self.__charset = charset  # mysql使用的字符编码,默认为utf8
        try:
            self.__connect_database = pymysql.connect(host=self.__host,
                                                      port=self.__port,
                                                      user=self.__username,
                                                      password=self.__password,
                                                      database=self.__db,
                                                      charset=self.__charset)
        except:
            log.error(traceback.format_exc())
            print('连接失败')
        else:
            self.cur = self.__connect_database.cursor()

    # 操作数据库（增删改查等）

    def query(self,sql):
        try:

            self.cur.execute(sql)
            result = self.cur.fetchall()  # 数据库查询返回结果
            self.cur.close()
        except Exception as e:
            log.info(f'查询结果为空:{e.args}')
        else:
            return result


    def update(self, sql):
        try:
            self.cur.execute(sql)
            self.__connect_database.commit()  # 增删改
            result1 = self.cur.fetchall()
            self.cur.close()

        except Exception as e:
            print('失败', e)
            self.__connect_database.rollback()
        else:
            return result1


    # 断开数据库连接
    def __del__(self):
        # 这是对象销毁前执行的方法
        self.__connect_database.close()


conn = MysqlConnectDatabase()

if __name__ == '__main__':
    # sql = "select del_flag from base_worker_info where id ='2444'"
    # if len(MysqlConnectDatabase().query(sql)): # 长度不为0表示存在，长度为0表示查询到的结果为空
    #     print(MysqlConnectDatabase().query(sql)[0][0])
    # else:
    #     print('查询为空')

    # cur_time = time.strftime("%Y-%m-%d %H:%M:%S")
    # print(cur_time)

    '''
    

    #--------------------  实际结果------------------------
    sql = "select sn,data from base_device_task where cmd=20"
    resData = []
    for item in MysqlConnectDatabase().query(sql):
        resData.append((item[0],json.loads(item[1])['user_id']))
    #------------------根据id查询人员的u_id------------------
    sql1 = "select u_id from base_worker_info where id = '2488'"
    u_id = MysqlConnectDatabase().query(sql1)[0][0]
    #---------------------拼接预期结果-----------------------
    sql2 = "select serial_number from base_device where device_type in (14,16,19,21,22,24,26,27)"
    expData = []
    for item in MysqlConnectDatabase().query(sql2):
        expData.append((item[0],u_id))

    print(sorted(expData) == sorted(resData))
        '''
    # sql_serial_number = "select serial_number from base_device where device_type in (14,15,16,19,21,22,24,26,27) and del_flag=0"
    # print(MysqlConnectDatabase().query(sql_serial_number))



    '''
    1- 预期结果VS实际结果
    2- 预期结果:{"sn":"xx","user_id":"xxx"} VS {"sn":"xx","user_id":"xxx"}
    '''



    # user_ids = []
    # for item in list(MysqlConnectDatabase().query(sql)):
    #    user_ids.append(json.loads(item[0])['user_id'])
    #

    # print(u_id)
    #
    # result = all([word in text for word in u_id for text in user_ids])
    # print(result)

    # list_string = [u_id]
    # list_text = ['hello letters', 'big hi letters', 'big superman letters']
    # result = all([word in text for word in list_string for text in list_text])
    # print(result)


    # sql2 = 'delete from base_worker_iris'
    # MysqlConnectDatabase().update(sql2)



    #print(type(MysqlConnectDatabase().query(sql)))
    #
    # sql = "select serial_number from base_device where device_type in (14,19,21,22,24,26,27) and del_flag=0"
    # print(conn.query(sql)[0][0])

    # sql =  "select id from attend_shifts where name='地面四签行政班3'"
    # print(conn.query(sql)[0][0])
    #
    # sql_uid = f"select u_id from base_worker_info where id=18"
    # print(conn.query(sql_uid)[0][0])

    # sql_uid = "select u_id from base_worker_info where del_flag=0 LIMIT 10"
    # uid = conn.query(sql_uid)
    #
    # for u_id in uid:
    #     print(u_id[0])


    sql = "update base_worker_info set name='王太平' where name='王太平y'"
    print(conn.update(sql))

